﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using PageAdmin.Utils.ORM.PropertyDescriptor;

namespace PageAdmin.Utils.ORM
{

    //常用方法演示，Model为实体类名，model为实体的实例，注意连接网站后要释放DbConnection对象，建议放在uising语句中
    //using (DataBaseContext dbContext = new SqlSugarDbContext("privideName", "connection"))
    //{
    //    dbContext.DbSet<Model>().QueryFirst();//查询一条
    //    dbContext.DbSet<Model>().Query();//查询
    //    dbContext.DbSet<Model>().Update(model); //更新
    //    dbContext.DbSet<Model>().Insert(model); //插入
    //    dbContext.DbSet<Model>().Delete(1); //删除
    //    dbContext.DbConnection.Query<Model>("sql"); //原生dapper语法，转换为实体Model
    //}                ;


    /// <summary>
    /// 数据表和实体的映射类，对dapper常用方法的封装,禁止外部项目直接实例化
    /// </summary>
    /// <typeparam name="T"></typeparam>
  
    public partial class DbSet<T> where T : class, new()
    {
        private IDbConnection _IDbConnection;
        private IDbTransaction _IDbTransaction;
        private int? _CommandTimeout;
        private DynamicParameters _DynamicDbParameters; //通过dbContext传递进来的参数。
        private bool _ExecutedInitDbParamter; //是否运行完毕后释放全局的参数对象。
        private double _CacheMinutes; //缓存过期时间，单位：分钟
        private string _Schema;//数据表的架构
        /// <summary>
        /// 实体对应的表名
        /// </summary>
        private string _TableName;
        /// <summary>
        /// 实体属性和字段的映射
        /// </summary>
        private IEnumerable<ModelFieldMapped> _ModelFieldMappingList;

        /// <summary>
        /// 实体类型
        /// </summary>
        private Type _ModelType;

        internal DbSet(IDbConnection dbConnection,string Schema,IDbTransaction dbTransaction = null, int? commandTimeout = null, DynamicParameters dynamicDbParameters = null, double cacheMinutes = 0, bool executedInitDbParamter = true)
        {

            this._IDbConnection = dbConnection;
            this._IDbTransaction = dbTransaction;
            this._CommandTimeout = commandTimeout;
            this._DynamicDbParameters = dynamicDbParameters;
            this._ExecutedInitDbParamter = executedInitDbParamter;
            this._Schema = Schema;
            this._ModelType = typeof(T);
            this._TableName = AttributeHelper.GetTableName(this._ModelType);
            this._ModelFieldMappingList = AttributeHelper.GetModelFieldMappingList(this._ModelType);
            this._CacheMinutes = cacheMinutes;
        }

        /// <summary>
        /// 实体类型
        /// </summary>
        /// <returns></returns>
        public Type ModelType
        {
            get
            {
                return this._ModelType;
            }
        }

        /// <summary>
        /// 获取表名
        /// </summary>
        /// <returns></returns>
        public string TableName
        {
            get
            {
                return this._TableName;
            }
        }

        /// <summary>
        /// 获取属性和字段的映射集合
        /// </summary>
        /// <returns></returns>
        public IEnumerable<ModelFieldMapped> ModelFieldMappingList
        {
            get
            {
                return this._ModelFieldMappingList;
            }
        }

        #region 查询方法


        /// <summary>
        /// 查询单个字段的值,返回指定的值类型，查询不到则返回默认值
        /// </summary>
        /// <typeparam name="Ttype">返回的值类型</typeparam>
        /// <param name="field"></param>
        /// <param name="condition"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public Ttype ExecuteScalar<Ttype>(string field, string condition = null, object param = null) where Ttype : struct
        {
            if (string.IsNullOrEmpty(field))
            {
                throw new Exception("无效的field参数！");
            }
            if (param == null)
            {
                param = _DynamicDbParameters;
            }
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = BuildSqlHelper.CreateSelectSql(table, field, condition);
            Ttype result = _IDbConnection.ExecuteScalar<Ttype>(sql, param, this._IDbTransaction, this._CommandTimeout);
            DisposeDbParamter();
            return result;
        }

        /// <summary>
        /// 根据条件查询记录是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbConnection"></param>
        /// <param name="condition"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public bool IsExists(string condition = null, object param = null)
        {
            if (param == null)
            {
                param = _DynamicDbParameters;
            }
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = $"if exists(select top 1 * from [{table}] {BuildSqlHelper.FormatCondition(condition)}) select 1 else select 0";
            int result = _IDbConnection.ExecuteScalar<int>(sql, param, this._IDbTransaction, this._CommandTimeout);
            DisposeDbParamter();
            return result == 1 ? true : false;
        }


        /// <summary>
        /// 根据参数查询，返回第一条数据的实体映射,查询不到则返回null
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="condition"></param>
        /// <param name="orderBy"></param>
        /// <param name="param"></param>
        /// <returns></returns>

        public T QueryFirst(string condition = null, string orderBy = null, object param = null)
        {
            if (param == null)
            {
                param = _DynamicDbParameters;
            }
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = BuildSqlHelper.CreateSelectSql<T>(table,condition, orderBy, 1);
            T result = _IDbConnection.QueryFirstOrDefault<T>(sql, param, this._IDbTransaction, this._CommandTimeout);
            DisposeDbParamter();
            return result;
        }

        /// <summary>
        /// 根据Id查询，并映射到实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public T Find(int id)
        {
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = BuildSqlHelper.CreateSelectSql<T>(table, "id=@id");
            T result = _IDbConnection.QueryFirstOrDefault<T>(sql, new { id = id }, this._IDbTransaction, this._CommandTimeout);
            return result;
        }

        /// <summary>
        /// 根据条件查询，返回实体类型的IEnumerable
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="orderBy"></param>
        /// <param name="showNumber"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> Query(string condition = null, string orderBy = null, int? showNumber = null, object param = null)
        {
            if (param == null)
            {
                param = _DynamicDbParameters;
            }
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = BuildSqlHelper.CreateSelectSql<T>(table,condition, orderBy, showNumber);
            IEnumerable<T> result = _IDbConnection.Query<T>(sql, param, this._IDbTransaction, true, this._CommandTimeout);
            DisposeDbParamter();
            return result;
        }

        /// <summary>
        /// 分页参数，返回实体类型的IEnumerable
        /// </summary>
        /// <param name="pageInfo"></param>
        /// <param name="condition"></param>
        /// <param name="orderBy"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> PagingQuery(PageInfo pageInfo, string condition = null, string orderBy = null, object param = null)
        {
            if (param == null)
            {
                param = _DynamicDbParameters;
            }
            string fields = BuildSqlHelper.GetSelectFields<T>();
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = BuildSqlHelper.CreateSelectSql(table, "count(1) as co", condition, null);
            int recordCount = _IDbConnection.ExecuteScalar<int>(sql, param, this._IDbTransaction, this._CommandTimeout);
            int pageSize = pageInfo.PageSize;
            int pageCount = (recordCount % pageSize == 0) ? (recordCount / pageSize) : (recordCount / pageSize + 1);
            pageInfo.RecordCount = recordCount;
            pageInfo.PageCount = pageCount;
            if (pageInfo.CurrentPage > pageCount)
            {
                pageInfo.CurrentPage = pageCount;
            }
            sql = BuildSqlHelper.CreateSelectSql(table, fields, condition, orderBy, pageInfo);
            IEnumerable<T> result = _IDbConnection.Query<T>(sql, param, this._IDbTransaction, true, this._CommandTimeout);
            DisposeDbParamter();
            return result;
        }


        /// <summary>
        /// 根据模型添加到缓存中
        /// </summary>
        /// <param name="dbContext"></param>
        /// <returns></returns>
        public IEnumerable<T> CacheQuery(string orderBy = null)
        {
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string key = table + "_model_cache";
            IEnumerable<T> dataList = CacheHelper.Get<IEnumerable<T>>(key);
            if (dataList == null)
            {
                dataList = Query(null, orderBy);
                CacheHelper.Add(key, dataList, _CacheMinutes);
            }
            return dataList;
        }

        #endregion


        #region insert方法封装
        /// <summary>
        /// 根据实体参数插入一条新的数据,并获取最新插入的Id
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Insert(T param)
        {
            return Insert(param, null);
        }

        /// <summary>
        /// 根据实体参数插入一条新的数据,并获取最新插入的Id
        /// </summary>
        /// <param name="param"></param>
        /// <param name="limitField">限制的字段</param>
        /// <returns></returns>
        public int Insert(T param, IEnumerable<string> limitField)
        {
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = BuildSqlHelper.CreateInsertSql<T>(table,limitField);
            sql += ";select CONVERT(int,SCOPE_IDENTITY())";
            return _IDbConnection.ExecuteScalar<int>(sql, param, this._IDbTransaction, this._CommandTimeout);
        }

        #endregion

        #region delete方法封装
        /// <summary>
        /// 根据id删除数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = $"delete from [{table}] where [id]=@id";
            return this._IDbConnection.Execute(sql, new { id = id }, this._IDbTransaction, this._CommandTimeout);
        }

        /// <summary>
        ///  根据自定义条件和参数删除数据
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Delete(string condition, object param = null)
        {
            if (param == null)
            {
                param = _DynamicDbParameters;
            }
            condition = BuildSqlHelper.FormatCondition(condition);
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = $"delete from [{table}] {condition}";
            return this._IDbConnection.Execute(sql, param, this._IDbTransaction, this._CommandTimeout);
        }

        #endregion


        #region update方法封装

        /// <summary>
        /// 根据自定义条件，更新表中的单个字段
        /// </summary>
        /// <param name="field"></param>
        /// <param name="fieldValue"></param>
        /// <param name="condition"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Update(string field, dynamic fieldValue, string condition, DynamicParameters param = null)
        {
            if (param == null)
            {
                param = _DynamicDbParameters;
            }
            condition = BuildSqlHelper.FormatCondition(condition);
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = $"update [{table}] set [{field}]=@field {condition}";
            if (param == null)
            {
                param = new DynamicParameters();
                param.Add("field", fieldValue);
            }
            else if (param.ParameterNames.Where(c => c.Equals(field, StringComparison.OrdinalIgnoreCase)).FirstOrDefault() == null)
            {
                param.Add("field", fieldValue);
            }
            int num = this._IDbConnection.Execute(sql, param, this._IDbTransaction, this._CommandTimeout);
            DisposeDbParamter();
            return num;
        }

        /// <summary>
        /// 根据自定义条件，更新表中的多个字段
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="condition"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Update(IDictionary<string, object> fields, string condition, DynamicParameters param = null)
        {
            if (param == null)
            {
                param = _DynamicDbParameters;
            }
            if (fields.Count() == 0)
            {
                return 0;
            }
            DbKeyValueList outParam = new DbKeyValueList();
            DbKeyValueList fieldList = new DbKeyValueList();
            foreach (var item in fields)
            {
                fieldList.Add(item.Key, item.Value);
            }
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = BuildSqlHelper.CreateUpdateSql(table, fieldList, condition, out outParam);
            if (param == null)
            {
                param = new DynamicParameters();
            }
            foreach (var item in outParam)
            {
                //检测参数中是否已经包含了item.Key
                if ((param.ParameterNames.Where(c => c.Equals(item.Key, StringComparison.OrdinalIgnoreCase)).FirstOrDefault() == null))
                {
                    param.Add(item.Key, item.Value);
                }
            }
            var num = this._IDbConnection.Execute(sql, param, this._IDbTransaction, this._CommandTimeout);
            DisposeDbParamter();
            return num;
        }

        /// <summary>
        /// 根据实体更新所有字段值，实体必须传递id
        /// </summary>
        /// <param name="param"></param>
        /// <param name="limitFields">限制更新的字段</param>
        /// <returns></returns>
        public int Update(T param)
        {
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = BuildSqlHelper.CreateUpdateSql<T>(table,"id=@id");
            return this._IDbConnection.Execute(sql, param, this._IDbTransaction, this._CommandTimeout);
        }

        /// <summary>
        /// 根据实体更新所有字段值，实体必须传递id
        /// </summary>
        /// <param name="param"></param>
        /// <param name="limitFields">限制更新的字段</param>
        /// <returns></returns>
        public int Update(T param, IEnumerable<string> limitFields)
        {
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = BuildSqlHelper.CreateUpdateSql<T>(table,limitFields, "id=@id");
            return this._IDbConnection.Execute(sql, param, this._IDbTransaction, this._CommandTimeout);
        }

        /// <summary>
        /// 根据实体更新所有字段值
        /// </summary>
        /// <param name="param"></param>
        /// <param name="limitFields"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int Update(T param, IEnumerable<string> limitFields, string condition)
        {
            string table = this._TableName;
            if (!string.IsNullOrEmpty(this._Schema))
            {
                table = this._Schema + "." + table;
            }
            string sql = BuildSqlHelper.CreateUpdateSql<T>(table,limitFields, condition);
            return this._IDbConnection.Execute(sql, param, this._IDbTransaction, this._CommandTimeout);
        }

        #endregion


        /// <summary>
        /// 释放全局参数资源
        /// </summary>
        private void DisposeDbParamter()
        {
            if (this._ExecutedInitDbParamter)
            {
                this._DynamicDbParameters = null;
            }
        }
    }

}